import java.util.Scanner;
import java.sql.*;
class UserSQL {
    private static final String URL = "jdbc:sqlserver://localhost:1433; DatabaseName=example";
    private static final String USER_NAME = "sa";
    private static final String PASSWORD = "123456";

    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
}
class select {
    public void select(){
        System.out.println("database example:");
        Connection conn = null;
        Statement stmt = null;
        try{
            conn= UserSQL.getConnection();
            stmt=conn.createStatement();
            String sql = "select * from student";
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next())
            {
                for(int i =1;i<6;i++)
                {System.out.print("  "+rs.getString(i)+"  ");}
                System.out.printf("\n");
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            if(stmt!=null){
                try{
                    stmt.close();
                }catch(Exception ex){}
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception ex){}
            }
        }
    }
}
class insert {
    public void insert(){
        System.out.println("database example:");
        System.out.println("请依次输入学号，姓名，性别，年龄，系名");
        Scanner sc1=new Scanner(System.in);
        int sno=sc1.nextInt();
        Scanner sc2=new Scanner(System.in);
        String sname=sc2.next();
        Scanner sc3=new Scanner(System.in);
        String ssex=sc3.next();
        Scanner sc4=new Scanner(System.in);
        String sage=sc4.next();
        Scanner sc5=new Scanner(System.in);
        String sdept=sc5.next();
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement stmt1=null;
        try{
            conn= UserSQL.getConnection();
            stmt=conn.createStatement();
            String sql1 = "insert into student (sno,sname,ssex,sage,sdept) values(?,?,?,?,?) ";
            stmt1 =  conn.prepareStatement(sql1);
            String sql = "select * from Student";
            ResultSet rs = stmt.executeQuery(sql);
            int m=0;
            while(rs.next())
            {
                int a=Integer.parseInt(rs.getString(1).trim());
                if(sno==a)
                {
                    m=1;
                }
            }
            if(m==1)
            {
                System.out.println("已有相同的学号");
            }
            else
            {
                stmt1.setInt(1,sno);
                stmt1.setString(2,sname);
                stmt1.setString(3,ssex);
                stmt1.setString(4,sage);
                stmt1.setString(5,sdept);
                stmt1.executeUpdate();
            }

        }catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            if(stmt!=null){
                try{
                    stmt.close();
                }catch(Exception ex){}
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception ex){}
            }
            if(stmt1!=null){
                try{
                    stmt1.close();
                }catch(Exception ex){}
            }
        }
    }
}

class delete {
    public void delete(){

        System.out.println("输入要删除的学号");
        Scanner sc1=new Scanner(System.in);
        int sno=sc1.nextInt();
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement stmt1=null;
        try{
            conn= UserSQL.getConnection();
            stmt=conn.createStatement();
            String sql1 = "delete  from student where sno=?";
            stmt1=conn.prepareStatement(sql1);
            String sql = "select * from Student";
            ResultSet rs = stmt.executeQuery(sql);
            int m=1;
            while(rs.next())
            {
                int a=Integer.parseInt(rs.getString(1).trim());
                if(sno==a)
                {
                    m=0;
                }
            }
            if(m==1)
            {
                System.out.println("无此学号");
            }
            else
            {
                stmt1.setInt(1,sno);
                stmt1.executeUpdate();
                System.out.println("删除了学号为"+sno+"的记录");
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            if(stmt!=null){
                try{
                    stmt.close();
                }catch(Exception ex){}
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception ex){}
            }
            if(stmt1!=null){
                try{
                    stmt1.close();
                }catch(Exception ex){}
            }
        }
    }
}
class update {
    public void update(){
        System.out.println("database example:");
        System.out.println("请依次输入学号，姓名，性别，年龄，系名");
        Scanner sc1=new Scanner(System.in);
        int sno=sc1.nextInt();
        Scanner sc2=new Scanner(System.in);
        String sname=sc2.next();
        Scanner sc3=new Scanner(System.in);
        String ssex=sc3.next();
        Scanner sc4=new Scanner(System.in);
        String sage=sc4.next();
        Scanner sc5=new Scanner(System.in);
        String sdept=sc5.next();
        Connection conn = null;
        Statement stmt = null;
        PreparedStatement stmt1=null;
        try{
            conn= UserSQL.getConnection();
            stmt=conn.createStatement();
            String sql1 = "update student set sname='"+sname+"',ssex='"+ssex+"',sage='"+sage+"',sdept='"+sdept+"' where sno='"+sno+"'";
            stmt1 =  conn.prepareStatement(sql1);

            String sql = "select * from Student";
            ResultSet rs = stmt.executeQuery(sql);
            int m=0;
            while(rs.next())
            {
                int a=Integer.parseInt(rs.getString(1).trim());
                if(sno==a)
                {
                    m=1;
                }
            }
            if(m==1)
            {
                stmt1.executeUpdate();
                System.out.println("已修改此表");
            }
            else
            {
                System.out.println("没有这个学号的同学");

            }

        }catch(Exception ex){
            ex.printStackTrace();
        }
        finally{
            if(stmt!=null){
                try{
                    stmt.close();
                }catch(Exception ex){}
            }
            if(conn!=null){
                try{
                    conn.close();
                }catch(Exception ex){}
            }
            if(stmt1!=null){
                try{
                    stmt1.close();
                }catch(Exception ex){}
            }
        }
    }
}

public class test{
    public static void main(String []args)
    {

        Scanner Scanner=new Scanner(System.in);
        boolean c=true;
        while(c){
            System.out.println("输入对数据库的操作：1.插入、2.删除、3.修改、4.查询或5.退出操作");
            String a=Scanner.next();
            switch (a){
                case "1":
                {
                    insert insert=new insert();
                    insert.insert();
                    break;
                }
                case "2":
                {
                    delete delete=new delete();
                    delete.delete();
                    break;
                }
                case "3":
                {
                    update update =new update();
                    update.update();
                    break;
                }
                case "4":
                {
                    select select=new select();
                    select.select();
                    break;
                }
                case "5":
                {
                    c=false;
                    System.out.println("End");
                    break;
                }
                default:
                    System.out.println("无效的操作指令");
                    break;
            }}
    }
}